package simulaco.dao;

import gminet.infra.dao.DataAccessObject;
import gminet.infra.db.TransactionException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import simulaco.dto.ConcursoMateria;
import simulaco.dto.QuestaoComplemento;
import simulaco.dto.QuestaoComplementoTipo;

public class QuestaoComplementoDAO extends DataAccessObject {

    public static QuestaoComplemento findByPK(QuestaoComplemento questaoComplemento, Connection conn)
            throws TransactionException {

        return findByPK(questaoComplemento.getConcursoMateria().getConcurso().getCodigo(), questaoComplemento
                .getConcursoMateria().getMateria().getCodigo(), questaoComplemento.getCodigo(), conn);
    }

    public static QuestaoComplemento findByPK(int concursoId, int materiaId, int complementoId, Connection conn)
            throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO       CD_CONCURSO     ");
        sbSql.append("     , A.CD_MATERIA        CD_MATERIA      ");
        sbSql.append("     , A.CD_COMPLEMENTO    CD_COMPLEMENTO  ");
        sbSql.append("     , A.TX_TEXTO          TX_TEXTO        ");
        sbSql.append("     , A.ID_TIPO           ID_TIPO         ");
        sbSql.append("  FROM QUESTAO_COMPLEMENTO    A ");
        sbSql.append(" WHERE A.CD_CONCURSO    = ? ");
        sbSql.append("   AND A.CD_MATERIA     = ? ");
        sbSql.append("   AND A.CD_COMPLEMENTO = ? ");

        QuestaoComplemento complemento = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, concursoId);
            setInt(ps, pos++, materiaId);
            setInt(ps, pos++, complementoId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                complemento = fromResultSet(rs);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoComplementoDAO.findByPK (" + sqle.getMessage() + ")");
        }
        return complemento;
    }

    public static List<QuestaoComplemento> findByConcursoMateria(ConcursoMateria concursoMateria, Connection conn) throws TransactionException {

        return findByConcursoMateria(concursoMateria.getConcurso().getCodigo(), concursoMateria.getMateria().getCodigo(), conn);
    }

    public static List<QuestaoComplemento> findByConcursoMateria(int concursoId, int materiaId, Connection conn)
            throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO       CD_CONCURSO     ");
        sbSql.append("     , A.CD_MATERIA        CD_MATERIA      ");
        sbSql.append("     , A.CD_COMPLEMENTO    CD_COMPLEMENTO  ");
        sbSql.append("     , A.TX_TEXTO          TX_TEXTO        ");
        sbSql.append("     , A.ID_TIPO           ID_TIPO         ");
        sbSql.append("  FROM QUESTAO_COMPLEMENTO    A ");
        sbSql.append(" WHERE A.CD_CONCURSO    = ? ");
        sbSql.append("   AND A.CD_MATERIA     = ? ");

        List<QuestaoComplemento> complementosList = new ArrayList<QuestaoComplemento>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, concursoId);
            setInt(ps, pos++, materiaId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                QuestaoComplemento complemento = fromResultSet(rs);
                complementosList.add(complemento);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoComplementoDAO.findAll (" + sqle.getMessage() + ")");
        }
        return complementosList;
    }

    public static void insert(QuestaoComplemento complemento, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("INSERT INTO QUESTAO_COMPLEMENTO ");
        sbSql.append("     ( CD_CONCURSO     ");
        sbSql.append("     , CD_MATERIA      ");
        sbSql.append("     , CD_COMPLEMENTO  ");
        sbSql.append("     , TX_TEXTO        ");
        sbSql.append("     , ID_TIPO         ");
        sbSql.append(") VALUES (?,?,?,?,?)   ");

        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, complemento.getConcursoMateria().getConcurso().getCodigo());
            setInt(ps, pos++, complemento.getConcursoMateria().getMateria().getCodigo());
            setInt(ps, pos++, complemento.getCodigo());
            setString(ps, pos++, complemento.getTexto());
            setInt(ps, pos++, complemento.getTipo().getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoComplementoDAO.insert (" + sqle.getMessage() + ")");
        }
    }

    public static void update(QuestaoComplemento complemento, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("UPDATE QUESTAO_COMPLEMENTO   ");
        sbSql.append("   SET TX_TEXTO          = ? ");
        sbSql.append("     , ID_TIPO           = ? ");
        sbSql.append(" WHERE CD_CONCURSO       = ? ");
        sbSql.append("   AND CD_MATERIA        = ? ");
        sbSql.append("   AND CD_COMPLEMENTO    = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setString(ps, pos++, complemento.getTexto());
            setInt(ps, pos++, complemento.getTipo().getCodigo());
            setInt(ps, pos++, complemento.getConcursoMateria().getConcurso().getCodigo());
            setInt(ps, pos++, complemento.getConcursoMateria().getMateria().getCodigo());
            setInt(ps, pos++, complemento.getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoComplementoDAO.update (" + sqle.getMessage() + ")");
        }
    }

    public static void delete(QuestaoComplemento complemento, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("DELETE FROM QUESTAO_COMPLEMENTO ");
        sbSql.append(" WHERE CD_CONCURSO    = ? ");
        sbSql.append("   AND CD_MATERIA     = ? ");
        sbSql.append("   AND CD_COMPLEMENTO = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, complemento.getConcursoMateria().getConcurso().getCodigo());
            setInt(ps, pos++, complemento.getConcursoMateria().getMateria().getCodigo());
            setInt(ps, pos++, complemento.getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoComplementoDAO.delete (" + sqle.getMessage() + ")");
        }
    }

    public static void deleteByConcursoMateria(ConcursoMateria concursoMateria, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("DELETE FROM QUESTAO_COMPLEMENTO ");
        sbSql.append(" WHERE CD_CONCURSO = ? ");
        sbSql.append("   AND CD_MATERIA  = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, concursoMateria.getConcurso().getCodigo());
            setInt(ps, pos++, concursoMateria.getMateria().getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoComplementoDAO.deleteByQuestao (" + sqle.getMessage() + ")");
        }
    }

    public static int getNextCodigo(ConcursoMateria concursoMateria, Connection conn) throws TransactionException {

        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("SELECT MAX(CD_COMPLEMENTO) AS CD_MAX ");
            sbSql.append("  FROM QUESTAO_COMPLEMENTO ");
            sbSql.append(" WHERE CD_CONCURSO = ? ");
            sbSql.append("   AND CD_MATERIA  = ? ");

            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, concursoMateria.getConcurso().getCodigo());
            setInt(ps, pos++, concursoMateria.getMateria().getCodigo());
            ResultSet rs = ps.executeQuery();
            int max = 0;
            if (rs.next()) {
                max = rs.getInt("CD_MAX");
            }
            close(rs);
            return max + 1;
        } catch (SQLException sqlEx) {
            throw new TransactionException(sqlEx);
        }
    }

    private static QuestaoComplemento fromResultSet(ResultSet rs) throws SQLException {

        // QUESTAO ITEM
        QuestaoComplemento complemento = new QuestaoComplemento();
        complemento.getConcursoMateria().getConcurso().setCodigo(getInt(rs, "CD_CONCURSO"));
        complemento.getConcursoMateria().getMateria().setCodigo(getInt(rs, "CD_MATERIA"));
        complemento.setCodigo(getInt(rs, "CD_COMPLEMENTO"));
        complemento.setTexto(getString(rs, "TX_TEXTO"));
        complemento.setTipo(QuestaoComplementoTipo.getTipoById(getByte(rs, "ID_TIPO")));
        //
        return complemento;
    }

}
